﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_PGM_Get_System_N_Pgm_List')
	BEGIN
		DROP Procedure usp_UPDMS_COM_PGM_Get_System_N_Pgm_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_PGM_Get_System_N_Pgm_List
**	Desc : 등록된 시스템 및 프로그램 정보를 가져온다.
**	Test Exec Query : Exec usp_UPDMS_COM_PGM_Get_System_N_Pgm_List 'ko'
**	Called by : Com_Dac_UPDMS_COM_PGM.cs
**	Program ID : ALL
**	Auth : 송시명
**	Date : 2011-02-11
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_PGM_Get_System_N_Pgm_List]
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT CASE rslt.Pgm_Div WHEN 'D' THEN SUBSTRING(rslt.Pgm_Nm, 11, LEN(rslt.Pgm_Nm))
                         ELSE '　　' + rslt.Pgm_Nm
       END Pgm_Nm,
       CASE rslt.Pgm_Div WHEN 'D' THEN ''
                         ELSE rslt.Pgm_Id
       END Pgm_Id,
       rslt.Pgm_Id AS Pgm_Id_All
  FROM (
       SELECT CASE @ls_lang_set WHEN 'ko' THEN '[' + ucp.Pgm_Id + ']' + ucp.Pgm_Nm
                                ELSE '[' + ucp.Pgm_Id + ']' + ucp.Pgm_Nm_Eng
              END Pgm_Nm,
              ucp.Pgm_Id,
              ucp.Pgm_Div,
              ucp.Dir_Depth
         FROM UPDMS_COM_PGM AS ucp WITH(NOLOCK)
       ) rslt
 ORDER BY rslt.Dir_Depth

GO